﻿using System.Collections.Generic;
using System.Linq;
using System.Security.Policy;
using System.Threading.Tasks;
using ThresholdAssistant.Autofac;
using ThresholdAssistant.WebAPI.Models;
using ThresholdAssistant.WebAPI.Models.Result.Data;

namespace ThresholdAssistant.WebAPI.Repository
{
    /// <summary>
    /// 数据记录表仓储
    /// </summary>
    public class RecordRepository : BaseRepository, IBaseAutofac
    {
        /// <summary>
        /// 根据组ID查询列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<RecordData>> GetListAsync(GetRecordListParam param)
        {
            var sqlWhere = " AND t.group_id = @GroupId";
            if (param.TagIds != null && param.TagIds.Any())
                sqlWhere += " AND t.tag_id IN @TagIds";
            if (param.SiteId > 0)
                sqlWhere += " AND t.site_id = @SiteId";
            

            var sql = string.Format(@"               
               SELECT ROW_NUMBER() OVER(ORDER BY tag_id ASC, t.id DESC) AS rowIndex
		            , t.`id`, t.`group_id` AS GroupId, t.`site_id` AS SiteId, t.`tag_id` AS TagId, t.`data_value` AS DataValue
	                , t.`create_id`, t.`create_time`, t.`update_time` AS UpdateTime, t.`remark` AS Remark, site.name AS SiteName
	                , tag.`name` AS TagName, tagtype.`name` AS TagTypeName
                FROM `t_record` t
	                INNER JOIN t_site site ON t.site_id = site.id
	                INNER JOIN t_tag tag ON t.tag_id = tag.id
	                INNER JOIN t_tagtype tagtype ON tag.tagtype_id = tagtype.id
                 WHERE 1 = 1 {0}
               ORDER BY t.tag_id
            ", sqlWhere);

            return await mydb.GetListAsync<RecordData>(sql, param);
        }

        /// <summary>
        /// 根据组ID查询列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<VRecordData>> GetVListAsync(long groupId)
        {
            var sql = @"               
                SELECT t.`group_id`, t.`site_id`, t.`tag_id`, t.site_name, t.tag_name
	                , t.tagtype_name, t.data_value
                FROM v_record t;
                  WHERE t.group_id = @groupId
                ORDER BY t.tag_id
            ";

            return await mydb.GetListAsync<VRecordData>(sql, new { groupId });
        }
        

        /// <summary>
        /// 新增
        /// </summary>
        public async Task<long> AddAsync(Record model)
        {
            string insertSql = @"
                INSERT INTO `t_record` (`group_id`, `site_id`, `tag_id`, `data_value`, `create_id`
	                , `create_time`, `update_time`, `remark`)
                VALUES (@GroupId, @SiteId, @TagId, @DataValue, @CreateId
	                , @CreateTime, @UpdateTime, @Remark);
                SELECT @@IDENTITY;
            ";
            return await mydb.ExecuteScalarAsync<long>(insertSql, model);
        }

        /// <summary>
        /// 查询
        /// </summary>
        public async Task<RecordData> GetInfoAsync(long id)
        {
            string querySql = @"
            SELECT ROW_NUMBER() OVER(ORDER BY tag_id ASC, t.id DESC) AS rowIndex
		            , t.`id`, t.`group_id` AS GroupId, t.`site_id` AS SiteId, t.`tag_id` AS TagId, t.`data_value` AS DataValue
	                , t.`create_id`, t.`create_time`, t.`update_time` AS UpdateTime, t.`remark` AS Remark, site.name AS SiteName
	                , tag.`name` AS TagName, tagtype.`name` AS TagTypeName
                FROM `t_record` t
	                INNER JOIN t_site site ON t.site_id = site.id
	                INNER JOIN t_tag tag ON t.tag_id = tag.id
	                INNER JOIN t_tagtype tagtype ON tag.tagtype_id = tagtype.id
                 WHERE t.id = @Id
               ORDER BY t.tag_id
            ";
            return await mydb.GetFirstOrDefaultAsync<RecordData>(querySql, new { Id = id });
        }

        /// <summary>
        /// 查询
        /// </summary>
        public async Task<IEnumerable<long>> GetIdListByTagIdListAsync(IEnumerable< long> tagIdList)
        {
            string querySql = @"select `id` from `t_record` where tag_id in @TagIdList;";
            return await mydb.GetListAsync<long>(querySql, new {  TagIdList = tagIdList });
        }

        /// <summary>
        /// 查询
        /// </summary>
        public async Task<IEnumerable<long>> GetIdListBySiteIdListAsync(IEnumerable<long> siteIdList)
        {
            string querySql = @"select `id` from `t_record` where site_id in @SiteIdList;";
            return await mydb.GetListAsync<long>(querySql, new { SiteIdList = siteIdList });
        }

        /// <summary>
        /// 查询
        /// </summary>
        public async Task<IEnumerable<Record>> GetDataListByTagIdGroupIdAsync(long tagId, long groupId)
        {
            string querySql = @"select
 `id`, `group_id`, `site_id`, `tag_id`, `data_value`, `create_id`, `create_time`, `update_time`,`remark`
 from `t_record`
 where group_id = @GroupId and tag_id;";
            return await mydb.GetListAsync<Record>(querySql, new { GroupId = groupId, TagId = tagId });
        }

        /// <summary>
        /// 删除
        /// </summary>
        public async Task<bool> DeleteAsync(long id)
        {
            string querySql = @"delete from `t_record` where id = @id;";
            return await mydb.ExecuteAsync(querySql, new { id });
        }

        /// <summary>
        /// 删除
        /// </summary>
        public async Task<bool> DeletesAsync(IEnumerable<long> idList)
        {
            string querySql = @"delete from `t_record` where id in @IdList;";
            return await mydb.ExecuteAsync(querySql, new { IdList = idList });
        }

        /// <summary>
        /// 删除
        /// </summary>
        public async Task<bool> DeletesByTagIdsAsync(IEnumerable<long> idList)
        {
            string querySql = @"delete from `t_record` where tag_id in @IdList;";
            return await mydb.ExecuteAsync(querySql, new { IdList = idList });
        }

        /// <summary>
        /// 删除
        /// </summary>
        public async Task<bool> DeletesByGroupIdAsync(long groupId)
        {
            string querySql = @"delete from `t_record` where group_id = @groupId;";
            return await mydb.ExecuteAsync(querySql, new { groupId });
        }

        /// <summary>
        /// 删除
        /// </summary>
        public async Task<bool> DeletesBySiteIdAsync(IEnumerable<long> idList)
        {
            string querySql = @"delete from `t_record` where site_id in @IdList;";
            return await mydb.ExecuteAsync(querySql, new { IdList = idList });
        }

        /// <summary>
        /// 修改
        /// </summary>
        public async Task<bool> UpdateAsync(RecordData model)
        {
            string updateSql = @"update
`t_record`
set  `group_id`=@GroupId, `site_id`=@SiteId, `tag_id`=@TagId, `data_value`=@DataValue, `create_id`=@CreateId, `create_time`=@CreateTime, `update_time`=@UpdateTime, `remark` = @Remark
 where `id` = @Id";
            return await mydb.ExecuteAsync(updateSql, model);
        }

        /// <summary>
        /// 修改
        /// </summary>
        public async Task<bool> UpdateValueAsync(RecordData model)
        {
            string updateSql = "update t_record SET data_value = @DataValue where id = @Id";
            return await mydb.ExecuteAsync(updateSql, model);
        }
    }
}
